<?php
namespace app\houtai\controller\user;
use app\houtai\controller\BaseController;
use think\Db;
use think\Request;

class ListController extends BaseController {
  /**
   *  @brief  : 用户管理-注册用户
   *  @return : page
   *  @details:
   */
  public function indexAction() {

    $conds = [];
    $order = [];
    $CHANNELS = $this->GetAdminChannel();
    $ACCOUNTS_TYPE = config('ACCOUNTS_TYPE');
    //////////////////////////////////////////////////////////////////////////////////////
    $channel = $this->request->param('channel/d', -1);
    if ($channel > 0 && array_key_exists($channel, $CHANNELS)) {
      $conds['a.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['a.Channel'][] = ['EQ', $channel];
    } else {
      $conds['a.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////////////////////////////
    $search = $this->request->param('search/s', '', 'trim');
    $same = $this->request->param('same/d', 0);
    $same = in_array($same, [1, 2, 3]) ? $same : 0;
    if (!empty($search)) {
      if ($same == 0) {
        if (is_numeric($search)) {
          $search = $this->request->param('search/d', 0);
          if ($search > 0) {
            $conds['a.UserID|a.GameID'] = $search;
          }
        } else {
          $conds['a.NickName'] = ['LIKE', "%{$search}%"];
        }
      } else {
        if ($same == 1) {
          $conds['a.LastLogonIP'] = $search; // 同登录IP
        } else if ($same == 2) {
          $conds['a.RegisterIP'] = $search; // 同注册IP
        } else if ($same == 3) {
          $conds['a.LastLogonMachine'] = $search; // 同登录机器
        } else {
          //
        }
      }
    }
    //////////////////////////////////////////////////////
    $sex = $this->request->param('sex/d', -1); // 0=未知; 1=男性; 2=女性; -1=所有;
    if ($sex >= 0 && $sex <= 2) {
      $conds['a.Sex'] = $sex;
    } else {
      $sex = -1;
    }
    //////////////////////////////////////////////////////
    $at = $this->request->param('at/d', -1);
    if ($at >= 0 && array_key_exists($at, $ACCOUNTS_TYPE)) {
      $conds['a.AccountsType'] = $at;
    }
    //////////////////////////////////////////////////////
    $time1 = $this->request->param('time1/s', '', 'trim');
    $time2 = $this->request->param('time2/s', '', 'trim');

    $aa = YmdHis2Unix($time1);
    $bb = YmdHis2Unix($time2);
    $ta = date('Y-m-d 00:00:00', $aa);
    $tb = date('Y-m-d 23:59:59', $bb);
    if ($aa > 0) {
      if ($bb > 0) {
        if ($aa <= $bb) {
          $conds['a.RegisterDate'] = ['BETWEEN TIME', [$ta, $tb]];
        } else {
          $conds['a.RegisterDate'] = ['>= TIME', $ta];
        }
      } else {
        $conds['a.RegisterDate'] = ['>= TIME', $ta];
      }
    } else {
      if ($bb > 0) {
        $conds['a.RegisterDate'] = ['<= TIME', $tb];
      }
    }

    //////////////////////////////////////////////////////////////////////////////////////
    $orderMap = [
      'A' => 'Channel',
      'B' => 'RegisterDate',
      'C' => 'UserID',
      'D' => 'GameID',
      'E' => 'RoomCard',
      'F' => 'LastLogonIPCount',
      'G' => 'RegisterIPCount',
      'H' => 'LastLogonMachineCount',
      'I' => 'MemberOrder',
      'J' => 'MasterOrder',
      'K' => 'Nullity',
    ];
    $this->orderField = array_key_exists($this->orderField, $orderMap) ? $this->orderField : 'B';
    $order[$orderMap[$this->orderField]] = $this->orderDirection;
    //////////////////////////////////////////////////////////////////////////////////////
    $list = table('CYBasicDB.dbo.AccountsInfo a', 'rydb.basic')->field([
      'a.AccountsType', 'a.Accounts', 'a.Compellation', 'a.PassPortID', 'a.RegisterMobile',
      'a.RegisterIP', 'a.LastLogonIP', 'a.UserID', 'a.GameID', 'a.Sex',
      'a.UserRight', 'a.NickName', 'a.RegisterDate', 'a.Nullity', 'a.Channel',
      'a.LastLogonDate', 'a.AccountImgUrl', 'a.GameLogonCount', 'a.LastLogonMachine',
      'ISNULL((SELECT count(*) FROM [CYBasicDB].[dbo].[AccountsInfo] WHERE LastLogonIP=a.LastLogonIP), 0)' => 'LastLogonIPCount', // 同IP登陆数
      'ISNULL((SELECT count(*) FROM [CYBasicDB].[dbo].[AccountsInfo] WHERE RegisterIP=a.RegisterIP), 0)'   => 'RegisterIPCount',    // 同IP注册数
      'ISNULL((SELECT count(*) FROM [CYBasicDB].[dbo].[AccountsInfo] WHERE LastLogonMachine=a.LastLogonMachine), 0)' => 'LastLogonMachineCount', // 同登陆机器数
      'ISNULL((SELECT SUM(d.[RoomCard]) FROM [CYBasicDB].dbo.AccountsGameCurrency d WHERE d.UserID = [a].[UserID]), 0)' => 'RoomCard',
    ])
    ->join('CYBasicDB.dbo.AccountsGameCurrency c','c.UserID = a.UserID', 'LEFT')
    ->where($conds)->order($order)->paginate();

    //////////////////////////////////////////////////////////////////////////////////////
    $info = table('CYBasicDB.dbo.AccountsInfo a', 'rydb.basic')->field([
      'ISNULL(SUM(CAST(c.[RoomCard] AS BIGINT)), 0)'    => 'RoomCard',
      'ISNULL(SUM(CAST(c.[Gold] AS BIGINT)), 0)'       => 'Score',
      'ISNULL(SUM(CAST(c.[BankGold] AS BIGINT)), 0)' => 'InsureScore',
    ])
    ->join('CYBasicDB.dbo.AccountsGameCurrency c',    'c.UserID = a.UserID', 'LEFT')
    ->where($conds)->find();
    //////////////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'CHANNELS' => $CHANNELS,
      'ACCOUNTS_TYPE' => $ACCOUNTS_TYPE,
      'channel' => $channel,
      'list'  => $list,
      'info'  => $info,
      'sex'   => $sex,
      'at'    => $at,
      'search'=> $search,
      'time1' => $time1,
      'time2' => $time2,
      'same'  => $same,
    ]);
     // dump($conds);die;
  }
  /**
   *  @brief  : 用户管理-注册用户统计
   *  @return : page
   *  @details:
   */
  public function trendAction() {
    $conds = [];
    $LOGIN_TYPE = config('LOGIN_TYPE');
    $CHANNELS = $this->GetAdminChannel();
    //////////////////////////////////////////////////////////////////////////////////////
    $type = $this->request->param('type/d', 0);
    $type = array_key_exists($type, $LOGIN_TYPE) ? $type : 0;
    if ($type > 0) {
      $conds['a.PlatformID'] = $type;
    }

    $time1 = $this->request->param('time1/s', '', 'trim,YmdHis2Unix');
    $time2 = $this->request->param('time2/s', '', 'trim,YmdHis2Unix');
    $time2 = ($time2 > 0 && $time2 <= time()) ? $time2 : time();
    $time1 = ($time1 > 0 && $time1 <= $time2) ? $time1 : time() - 15 * 24 * 60 * 60; // 默认半个月的数据
    
    $time1 = date('Y-m-d 00:00:00', $time1);
    $time2 = date('Y-m-d 23:59:59', $time2);
    $conds['a.RegisterDate'] = ['BETWEEN TIME', [$time1, $time2]];

    $channel = $this->request->param('channel/d', -1);
    if ($channel > 0 && array_key_exists($channel, $CHANNELS)) {
      $conds['a.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['a.Channel'][] = ['EQ', $channel];
    } else {
      $conds['a.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////////////////////////////////////////////////////////////
    $list = [];
    $sql2 = table('CYBasicDB.dbo.AccountsInfo a', 'rydb.basic')->field([
      'CONVERT(VARCHAR(10), [a].[RegisterDate], 120)' => 'y',
      'COUNT(*)' => 'c',
    ])
    ->group('CONVERT(VARCHAR(10), [a].[RegisterDate], 120)')->where($conds)->buildSql();
    $total = db('', 'rydb.basic')->table("{$sql2} b")->count();  // 按日期分组满足条件有几条
    if ($total > 0) {
      $list = table('CYBasicDB.dbo.AccountsInfo a', 'rydb.basic')->field([
        'CONVERT(VARCHAR(10), [a].[RegisterDate], 120)' => 'y',
        'DATEPART(hh, [a].[RegisterDate])' => 'h',
        'COUNT(*)' => 'c',
      ])
      ->group('CONVERT(VARCHAR(10), [a].[RegisterDate], 120), DATEPART(hh, [a].[RegisterDate])')
      ->where($conds)->order(['y' => 'DESC', 'h'])->select(); // 按日期小时分组
    }
    $path = Request::instance()->baseUrl();
    $page = new \org\dtcms($list, $total <= 0 ? 1 : $total, 1, 10, false, ['path' => $path]);
    //////////////////////////////////////////////////////////////////////////////////////
    $tree = [];
    foreach($list as $a) {
      $y = $a['y'];
      if (!isset($tree[$y])) {
        $tree[$y] = ['total' => 0, 'child' => []];
      }
      $tree[$y]['total'] += $a['c'];
      $tree[$y]['child'][$a['h']] = $a;
    }
    //////////////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'type'  => $type,
      'page'  => $page,
      'tree'  => $tree,
      'list'  => $list,
      'time1' => $time1,
      'time2' => $time2,
      'channel' => $channel,
      'LOGIN_TYPE'  => $LOGIN_TYPE,
      'CHANNELS'    => $CHANNELS,
    ]);
  }
  /**
   *  @brief  : 用户管理-新用户走势图
   *  @return : page
   *  @details:
   */
  public function chartsAction() {
//    $conds = ['AccountsType' => 0];
    $CHANNELS = $this->GetAdminChannel();
    $DAY_SPAN = 14;
    $time2 = $this->request->param('time/s', 0, 'trim,YmdHis2Unix');
    $time2 = ($time2 > 0 && $time2 <= time()) ? $time2 : time();
    $time1 = $time2 - $DAY_SPAN * 24 * 60 * 60; // 默认两周的数据
    $time1 = date('Y-m-d 00:00:00', $time1);
    $time3 = date('Y-m-d 00:00:00', $time2 + 1 * 24 * 60 * 60);
    $conds['RegisterDate'] = ['BETWEEN TIME', [$time1, $time3]];
    $conds['Channel'] = ['IN', array_keys($CHANNELS)];
    ///////////////////////////////////////////////////////
    $list = table('AccountsInfo', 'rydb.basic')->field([
      'CONVERT(VARCHAR(10), RegisterDate, 120)' => 'a', 'Channel' => 'b', 'COUNT(UserID)' => 'c',
    ])
    ->where($conds)
    ->group('CONVERT(VARCHAR(10), RegisterDate, 120), Channel')
    ->select();
    ///////////////////////////////////////////////////////
    return $this->fetch('', [
      'time'    => date('Y-m-d', $time2),
      'list'    => $list,
      'CHANNELS'=> $CHANNELS,
      'DAY_SPAN'=> $DAY_SPAN,
    ]);
  }
}
